import pymysql

dic = {"win_id": {"org_id": 0, "turnover": 0}}

# 连接数据库
db = pymysql.connect(host='120.24.60.17', port=3306, user="root", passwd="1234", db="res")
# 使用cursor()方法获取操作游标
cursor = db.cursor()

"""获取所有org和win的对应关系"""
cursor.execute("SELECT id,organization_id FROM WindowInfo ;")
# where time>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
for (win_id, org_id) in cursor.fetchall():
    print(org_id, win_id)
    dic[win_id] = {"org_id": org_id, "turnover": 0}

"""获取近一周的所有订单"""
cursor.execute("SELECT * FROM Bill;")
bills = cursor.fetchall()
for bill in bills:
    dic[bill[2]]["turnover"] += bill[3]

"""开始收租"""
for _win_id in dic.keys():
    _salary = 100
    if 0 < dic[_win_id]["turnover"] <= 20000:
        _salary += dic[_win_id]["turnover"] * 0.05
    elif dic[_win_id]["turnover"] > 20000:
        _salary += 20000 * 0.05
        _salary += (dic[_win_id]["turnover"] - 20000) * 0.1
    sql = "INSERT INTO collect_rent(org_id,win_id,turnover,salary)  " \
          "VALUES(\'{}\',\'{}\',\'{}\',{});".format(dic[_win_id]["org_id"], _win_id, dic[_win_id]["turnover"],
                                                    dic[_win_id]["turnover"] - _salary)

    try:
        # 执行SQL语句
        cursor.execute(sql)
        db.commit()
        print(sql + "成功")
    except:
        db.rollback()
        print(sql + "_失败")
